﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlServerDataTableToExcel.BLL
{
    public class Excel
    {
        public static void ExportToExcel(List<Model.DataTableMsg> tableMsg, string fileName)
        {
            if (tableMsg.Count() == 0)
            {
                throw new Exception("没有需要保存的表结构");
            }
            if (string.IsNullOrWhiteSpace(fileName))
            {
                throw new Exception("保存文件不能为空");
            }

            short RowHeight = 15 * 20;
            HSSFWorkbook workbook;
            if (File.Exists(fileName))
            {
                FileStream excelfs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Read);
                workbook = new HSSFWorkbook(excelfs);
                excelfs.Close();
            }
            else
            {
                workbook = new HSSFWorkbook();
            }


            ///valueStyel 
            IFont valuefont = workbook.CreateFont();//创建字体样式  
            valuefont.Color = HSSFColor.Black.Index;//设置字体颜色 
            // cell.CellStyle = valueStyel;
            valuefont.FontName = "微软雅黑";
            ICellStyle valueStyel = workbook.CreateCellStyle();//创建单元格样式  s
            valueStyel.BorderBottom = BorderStyle.Thin;
            valueStyel.BorderLeft = BorderStyle.Thin;
            valueStyel.BorderRight = BorderStyle.Thin;
            valueStyel.BorderTop = BorderStyle.Thin;
            valueStyel.SetFont(valuefont);

            ///Stype
            IFont font = workbook.CreateFont();//创建字体样式  
            font.Color = HSSFColor.Black.Index;//设置字体颜色 
            font.IsBold = true;
            font.FontName = "Verdana";
            ICellStyle style = workbook.CreateCellStyle();//创建单元格样式  
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;
            style.FillPattern = FillPattern.SolidForeground;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;

            style.SetFont(font);


            HSSFRow row;
            HSSFCell cell;

            #region  设置索引
            HSSFSheet sheetFirst = (HSSFSheet)workbook.GetSheet("数据库表结构");
            if (sheetFirst != null)
            {
                for (int i = 1; i <= sheetFirst.LastRowNum; i++)
                {
                    string cellTableName = GetCellValue(sheetFirst, i, 0);
                    foreach (var table in tableMsg)
                    {
                        if (table.TableName == cellTableName)
                        {
                            string tableExplain_old = GetCellValue(sheetFirst, i, 1);
                            if (string.IsNullOrWhiteSpace(table.TableExplain))
                            {
                                table.TableExplain = GetCellValue(sheetFirst, i, 1);
                            }
                            break;
                        }
                    }
                    sheetFirst.RemoveRow(sheetFirst.GetRow(i));
                }
                if (sheetFirst.LastRowNum != 0)
                {
                    sheetFirst.RemoveRow(sheetFirst.GetRow(0));
                }
            }
            else
            {
                sheetFirst = workbook.CreateSheet("数据库表结构") as HSSFSheet;
            }

            sheetFirst.SetColumnWidth(0, 25 * 256);
            sheetFirst.SetColumnWidth(1, 25 * 256);

            row = sheetFirst.CreateRow(0) as HSSFRow;
            row.Height = RowHeight;
            cell = SetRowValue(row, 0, "表名称");
            cell.CellStyle = style;
            cell = SetRowValue(row, 1, "说明");
            cell.CellStyle = style;
            cell = SetRowValue(row, 2, "类型");
            cell.CellStyle = style;

            int index = 1;

            foreach (var table in tableMsg)
            {

                row = sheetFirst.CreateRow(index) as HSSFRow;
                row.Height = RowHeight;

                //表明
                cell = SetRowValue(row, 0, table.TableName);

                HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
                // strTableName 这个参数为 sheet名字 A1 为单元格 其他是固定格式
                if (table.TableName.Length > 31)
                {
                    link.Address = "#" + table.TableID + "!A1";
                }
                else
                {
                    link.Address = "#" + table.TableName + "!A1";
                }
                //设置 cellTableName 单元格 的连接对象
                cell.Hyperlink = link;
                cell.CellStyle = valueStyel;

                table.ExcelIndex = index + 1;

                //说明
                cell = SetRowValue(row, 1, table.TableExplain);
                cell.CellStyle = valueStyel;
                //类别
                cell = SetRowValue(row, 2, table.TableType);
                cell.CellStyle = valueStyel;

                index++;
            }
            #endregion



            foreach (var table in tableMsg)
            {

                #region 设置表明详细列表

                string sheetname = table.TableName;
                if (sheetname.Length > 31)
                {
                    sheetname = table.TableID;
                }
                HSSFSheet sheet = (HSSFSheet)workbook.GetSheet(sheetname);
                if (sheet != null)
                {
                    for (int i = 4; i <= sheet.LastRowNum; i++)
                    {
                        string cellColName = GetCellValue(sheet, i, 0);
                        foreach (var col in table.TableCols)
                        {
                            if (col.ColName == cellColName)
                            {
                                string oldColExp = GetCellValue(sheet, i, 7);
                                if (!string.IsNullOrWhiteSpace(oldColExp))
                                    col.ColExp = oldColExp;
                                break;
                            }
                        }
                    }
                    ///删除行
                    //for (int i = sheet.LastRowNum; i >= 0; i--)
                    //{
                    //    if (sheet.GetRow(i) != null)
                    //    {
                    //        sheet.RemoveRow(sheet.GetRow(i));  //只删除数据，保留格式
                    //    }
                    //}
                    for (int i = sheet.LastRowNum; i >= 1; i--)
                    {
                        sheet.ShiftRows(i, i + 1, -1);   //完全删除行
                    }
                }
                else
                {
                    sheet = workbook.CreateSheet(sheetname) as HSSFSheet;
                }
                ///移除合并单元格
                //int MergedCount = sheet.NumMergedRegions;
                //for (int i = MergedCount - 1; i >= 0; i--)
                //{
                //    sheet.RemoveMergedRegion(i);
                //}

                sheet.SetColumnWidth(0, 30 * 256);
                sheet.SetColumnWidth(1, 15 * 256);
                sheet.SetColumnWidth(2, 10 * 256);
                sheet.SetColumnWidth(3, 10 * 256);
                sheet.SetColumnWidth(4, 7 * 256);
                sheet.SetColumnWidth(5, 11 * 256);
                sheet.SetColumnWidth(6, 12 * 256);
                sheet.SetColumnWidth(7, 30 * 256);

                //第一行
                row = sheet.CreateRow(0) as HSSFRow;

                row.Height = RowHeight;
                cell = SetRowValue(row, 0, "返回表结构列表");

                HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
                // strTableName 这个参数为 sheet名字 A1 为单元格 其他是固定格式
                link.Address = "#数据库表结构!A" + table.ExcelIndex.ToString();
                //设置 cellTableName 单元格 的连接对象
                cell.Hyperlink = link;
                //第二行
                row = sheet.CreateRow(1) as HSSFRow;
                row.Height = RowHeight;
                cell = SetRowValue(row, 0, "TableName");
                cell.CellStyle = style;//为单元格设置显示样式 
                sheet.AddMergedRegion(new CellRangeAddress(1, 1, 1, 7));
                cell = SetRowValue(row, 1, table.TableName);
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = row.CreateCell(2) as HSSFCell;
                cell.CellStyle = style;
                cell = row.CreateCell(3) as HSSFCell;
                cell.CellStyle = style;
                cell = row.CreateCell(4) as HSSFCell;
                cell.CellStyle = style;
                cell = row.CreateCell(5) as HSSFCell;
                cell.CellStyle = style;
                cell = row.CreateCell(6) as HSSFCell;
                cell.CellStyle = style;
                cell = row.CreateCell(7) as HSSFCell;
                cell.CellStyle = style;

                //第三行
                row = sheet.CreateRow(2) as HSSFRow;
                row.Height = RowHeight;
                cell = SetRowValue(row, 0, "TableDesc");
                cell.CellStyle = style;//为单元格设置显示样式 
                sheet.AddMergedRegion(new CellRangeAddress(2, 2, 1, 7));
                cell = SetRowValue(row, 1, table.TableExplain);
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(2) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(3) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(4) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(5) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(6) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(7) as HSSFCell;
                cell.CellStyle = valueStyel;


                //第四行 表头
                row = sheet.CreateRow(3) as HSSFRow;
                row.Height = RowHeight;
                cell = SetRowValue(row, 0, "ColumnName");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 1, "DataType");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 2, "Length");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 3, "Precision");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 4, "Scale");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 5, "AllowNull");
                cell.CellStyle = style;//为单元格设置显示样式 
                cell = SetRowValue(row, 6, "Default");
                cell.CellStyle = style;//为单元格设置显示样式 Desc.
                cell = SetRowValue(row, 7, "Desc.");
                cell.CellStyle = style;//为单元格设置显示样式 

                //表体
                index = 4;
                foreach (var col in table.TableCols)
                {
                    row = sheet.CreateRow(index) as HSSFRow;
                    // row.RemoveAllCells();
                    row.Height = RowHeight;
                    cell = SetRowValue(row, 0, col.ColName);
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 1, col.ColType);
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 2, col.ColLength);
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 3, col.decimalLength);
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 4, "");
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 5, col.ColNull.ToString());
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 6, col.ColDefault);
                    cell.CellStyle = valueStyel;
                    cell = SetRowValue(row, 7, col.ColExp);
                    cell.CellStyle = valueStyel;
                    index++;
                }

                //表尾
                row = sheet.CreateRow(index) as HSSFRow;
                row.Height = RowHeight;
                cell = SetRowValue(row, 0, "PrimaryKey");
                cell.CellStyle = valueStyel;
                cell.CellStyle = style;//为单元格设置显示样式 
                sheet.AddMergedRegion(new CellRangeAddress(index, index, 1, 7));
                cell = SetRowValue(row, 1, table.KeyName);
                cell.CellStyle = valueStyel;

                cell = row.CreateCell(2) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(3) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(4) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(5) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(6) as HSSFCell;
                cell.CellStyle = valueStyel;
                cell = row.CreateCell(7) as HSSFCell;
                cell.CellStyle = valueStyel;

                #endregion
            }


            FileStream file = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            workbook.Write(file);
            file.Close();

        }

        private static string GetCellValue(HSSFSheet sheet, int RowIndex, int ColIndex)
        {
            /* Excel数据Cell有不同的类型，当我们试图从一个数字类型的Cell读取出一个字符串并写入数据库时，就会出现Cannot get a text value from a numeric cell的异常错误。
                     * 解决办法：先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
                     */
            if (sheet.GetRow(RowIndex) == null)
            {
                return "";
            }

            ICell cellValue = sheet.GetRow(RowIndex).GetCell(ColIndex);
            /* Excel数据Cell有不同的类型，当我们试图从一个数字类型的Cell读取出一个字符串并写入数据库时，就会出现Cannot get a text value from a numeric cell的异常错误。
             * 解决办法：先设置Cell的类型，然后就可以把纯数字作为String类型读进来了
             */
            if (cellValue == null)
            {
                return "";
            }
            cellValue.SetCellType(CellType.String);
            return cellValue.StringCellValue;
        }

        private static HSSFCell SetRowValue(HSSFRow row, int ColIndex, string cellValue)
        {
            HSSFCell cell = row.CreateCell(ColIndex) as HSSFCell;
            cell.SetCellType(CellType.String);
            cell.SetCellValue(cellValue);

            return cell;
        }

    }
}
